package com.guldan.thrall.util;


import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import com.guldan.core.enums.ValidatingRegisterTypeEn;
import com.guldan.core.exception.BusinessException;
import com.guldan.core.utils.MathUtil;
import com.guldan.core.utils.ProjectUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.fastjson.JSON;
import com.guldan.jaina.dto.register.RegisterTemplateDataDTO;
import com.guldan.jaina.dto.register.RegisterTemplateXlsDTO;
import com.guldan.core.constants.XlsHeaderConstant;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


public class RegisterExcel {
    protected Logger log = LoggerFactory.getLogger(getClass());

    public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
    public static final String EMPTY = "";
    public static final String POINT = ".";
    public static final String NOT_EXCEL_FILE = " : Not the Excel file!";
    public static final String PROCESSING = "Processing...";

    /**
     * read the Excel file
     *
     * @param path the path of the Excel file
     * @return
     * @throws IOException
     */
    public RegisterTemplateXlsDTO readExcel(String path) throws IOException {

        log.info("begin readExcel");

        if (path == null || EMPTY.equals(path)) {
            throw new BusinessException("没有发现上传的文件!");
        } else {
            String postfix = getPostfix(path);
            if (!EMPTY.equals(postfix)) {

                log.info("begin readExcel postfix " + postfix);

                if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    return readXls(path);
                }
                /*else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                    return readXlsx(path);
                } */
                else {
                    throw new BusinessException("文件格式不正确，只能为Microsoft Excel 97-2003 工作表格式，.xls后缀");
                }
            } else {
                log.info(path + NOT_EXCEL_FILE);
                throw new BusinessException(path + NOT_EXCEL_FILE);
            }
        }
    }

    /**
     * Read the Excel 2010
     *
     * @param path the path of the excel file
     * @return
     * @throws IOException
     */
    public RegisterTemplateXlsDTO readXlsx(String path) throws IOException {
        System.out.println(PROCESSING + path);
        InputStream is = new FileInputStream(path);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        RegisterTemplateXlsDTO dto = null;
        List<RegisterTemplateDataDTO> list = new ArrayList<RegisterTemplateDataDTO>();
        dto.setList(list);
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            int getLastRowNum = xssfSheet.getLastRowNum();
            int getFirstRowNum = xssfSheet.getFirstRowNum();
            int getPhysicalNumberOfRows = xssfSheet.getPhysicalNumberOfRows();
            log.info("xssfSheet.getPhysicalNumberOfRows():" + xssfSheet.getPhysicalNumberOfRows());
            // Read the Row
            int extSize = 0;
            for (int rowNum = 0; rowNum <= xssfSheet.getPhysicalNumberOfRows(); rowNum++) {

                try {
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    if (xssfRow != null) {
                        int total = xssfRow.getPhysicalNumberOfCells();
                        if (rowNum == 0) { // header
                            String[] header = new String[total - XlsHeaderConstant.REGISTER_XLS_HEADERS.length];
                            extSize = total - XlsHeaderConstant.REGISTER_XLS_HEADERS.length;
                            for (int i = 0; i < extSize; i++) {
                                String extName = xssfRow.getCell(i + XlsHeaderConstant.REGISTER_XLS_HEADERS.length).getStringCellValue();
                                header[i] = extName;
                            }
                            dto.setHeader(header);
                            continue;
                        }

                        System.out.println("rowNum:" + rowNum);
                        RegisterTemplateDataDTO dataDto = new RegisterTemplateDataDTO();

                        String name = xssfRow.getCell(0) == null ? "" : xssfRow.getCell(0).getStringCellValue();
                        String identNumber = xssfRow.getCell(1) == null ? "" : xssfRow.getCell(1).getStringCellValue();
                        String bankNumber = xssfRow.getCell(2) == null ? "" : xssfRow.getCell(2).getStringCellValue();
                        String areaName = xssfRow.getCell(3) == null ? "" : xssfRow.getCell(3).getStringCellValue();
                        String projectName = xssfRow.getCell(4) == null ? "" : xssfRow.getCell(4).getStringCellValue();
                        String year = xssfRow.getCell(5).getStringCellValue();
                        String subsidyCount = xssfRow.getCell(6).getStringCellValue();
                        //double money = xssfRow.getCell(7) == null ? 0 : xssfRow.getCell(7).getNumericCellValue();

                        double money = 0;
                        try {
                            money = xssfRow.getCell(7) == null ? 0 : xssfRow.getCell(7).getNumericCellValue();
                        } catch (Exception ex) {
                            money = xssfRow.getCell(7) == null ? 0 : Double.parseDouble(xssfRow.getCell(7).getStringCellValue());
                        }


                        dataDto.setName(name);
                        dataDto.setIdentNumber(identNumber);
                        dataDto.setBankNumber(bankNumber);
                        dataDto.setAreaName(areaName);
                        dataDto.setProjectName(projectName);
                        dataDto.setYear(Integer.parseInt(year));
                        dataDto.setSubsidyCount(Integer.parseInt(subsidyCount));
                        dataDto.setMoney(new BigDecimal(money));
                        BigDecimal[] values = new BigDecimal[total - XlsHeaderConstant.REGISTER_XLS_HEADERS.length];

                        String[] header = new String[extSize];
                        for (int i = 0; i < extSize; i++) {
                            XSSFCell xSSFCell = xssfRow.getCell(i + XlsHeaderConstant.REGISTER_XLS_HEADERS.length);
                            if (xSSFCell == null)
                                values[i] = BigDecimal.ZERO;
                            else {
                                try {
                                    values[i] = new BigDecimal(xSSFCell.getNumericCellValue());
                                } catch (Exception ex) {
                                    values[i] = new BigDecimal(xSSFCell.getStringCellValue());
                                }
                            }
                        }
                        dataDto.setValues(values);
                        list.add(dataDto);
                    }
                } catch (Exception ex) {

                }
            }
        }
        return dto;
    }

    /**
     * Read the Excel 2003-2007
     *
     * @param path the path of the Excel
     * @return
     * @throws IOException
     */
    //public List<RegisterTemplateDataDTO> readXls(String path) throws IOException {
    public RegisterTemplateXlsDTO readXls(String path) throws IOException {
        log.info(PROCESSING + path);
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        RegisterTemplateXlsDTO dto = new RegisterTemplateXlsDTO();
        List<RegisterTemplateDataDTO> list = new ArrayList<RegisterTemplateDataDTO>();
        dto.setList(list);
        // Read the Sheet

        if (hssfWorkbook == null || hssfWorkbook.getNumberOfSheets() < 1 || hssfWorkbook.getSheetAt(0) == null) {
            throw new BusinessException(ValidatingRegisterTypeEn.execlNoRecord.getMean(), ValidatingRegisterTypeEn.execlNoRecord.getCode().toString());
        }

        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {

            if (numSheet == 1) {
                //只读取第一个表的信息
                break;
            }

            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // Read the Row
            int extSize = 0;

            //取得execl前四行值
            //取，项目名称
            String projectName;
            if (hssfSheet.getRow(0) != null && getStringCellValue(hssfSheet.getRow(0).getCell(0)).equals(XlsHeaderConstant.REGISTER_XLS_COMMON_FIELD[0])) {
                projectName = getStringCellValue(hssfSheet.getRow(0).getCell(1));
                if (StringUtils.isBlank(projectName)) {
                    throw new BusinessException(String.format(ValidatingRegisterTypeEn.emptyField.getMean(), XlsHeaderConstant.REGISTER_XLS_COMMON_FIELD[0]), ValidatingRegisterTypeEn.emptyField.getCode().toString());
                }
            } else {
                throw new BusinessException(String.format(ValidatingRegisterTypeEn.emptyFieldName.getMean(), XlsHeaderConstant.REGISTER_XLS_COMMON_FIELD[0]), ValidatingRegisterTypeEn.emptyFieldName.getCode().toString());
            }

            //取，年度
            Integer year = 0;
            if (hssfSheet.getRow(1) != null && getStringCellValue(hssfSheet.getRow(1).getCell(0)).equals(XlsHeaderConstant.REGISTER_XLS_COMMON_FIELD[1])) {
                try {
                    year = Integer.parseInt(getStringCellValue(hssfSheet.getRow(1).getCell(1)));
                    if (!ProjectUtils.years.contains(year)) {
                        throw new BusinessException(String.format(ValidatingRegisterTypeEn.formatError.getMean() + "，可填值为%s", XlsHeaderConstant.REGISTER_XLS_COMMON_FIELD[1], ProjectUtils.years.toString())
                                , ValidatingRegisterTypeEn.formatError.getCode().toString());
                    }
                } catch (Exception ex) {
                    throw new BusinessException(String.format(ValidatingRegisterTypeEn.formatError.getMean(), XlsHeaderConstant.REGISTER_XLS_COMMON_FIELD[1])
                            , ValidatingRegisterTypeEn.formatError.getCode().toString());
                }
            } else {
                throw new BusinessException(String.format(ValidatingRegisterTypeEn.emptyFieldName.getMean(), XlsHeaderConstant.REGISTER_XLS_COMMON_FIELD[1])
                        , ValidatingRegisterTypeEn.emptyFieldName.getCode().toString());
            }

            //Integer year = Integer.parseInt(getStringCellValue(hssfSheet.getRow(1).getCell(1)));
            //取，发放次数
            //Integer subsidyCount = Integer.parseInt(getStringCellValue(hssfSheet.getRow(2).getCell(1)));
            Integer subsidyCount = 0;
            if (hssfSheet.getRow(2) != null && getStringCellValue(hssfSheet.getRow(2).getCell(0)).equals(XlsHeaderConstant.REGISTER_XLS_COMMON_FIELD[2])) {
                try {
                    subsidyCount = Integer.parseInt(getStringCellValue(hssfSheet.getRow(2).getCell(1)));
                } catch (Exception ex) {
                    throw new BusinessException(String.format(ValidatingRegisterTypeEn.formatError.getMean(), XlsHeaderConstant.REGISTER_XLS_COMMON_FIELD[2])
                            , ValidatingRegisterTypeEn.formatError.getCode().toString());
                } finally {
                    if (subsidyCount < 1) {
                        throw new BusinessException(String.format(ValidatingRegisterTypeEn.formatError.getMean(), XlsHeaderConstant.REGISTER_XLS_COMMON_FIELD[2])
                                , ValidatingRegisterTypeEn.formatError.getCode().toString());
                    }
                }
            } else {
                throw new BusinessException(String.format(ValidatingRegisterTypeEn.emptyFieldName.getMean(), XlsHeaderConstant.REGISTER_XLS_COMMON_FIELD[2])
                        , ValidatingRegisterTypeEn.emptyFieldName.getCode().toString());
            }

            //取，登记区域
            //String areaName = getStringCellValue(hssfSheet.getRow(3).getCell(1));
            String areaName;
            if (hssfSheet.getRow(3) != null && getStringCellValue(hssfSheet.getRow(3).getCell(0)).equals(XlsHeaderConstant.REGISTER_XLS_COMMON_FIELD[3])) {
                areaName = getStringCellValue(hssfSheet.getRow(3).getCell(1));
                if (StringUtils.isBlank(areaName)) {
                    throw new BusinessException(String.format(ValidatingRegisterTypeEn.emptyField.getMean(), XlsHeaderConstant.REGISTER_XLS_COMMON_FIELD[3])
                            , ValidatingRegisterTypeEn.emptyField.getCode().toString());
                }
            } else {
                throw new BusinessException(String.format(ValidatingRegisterTypeEn.emptyFieldName.getMean(), XlsHeaderConstant.REGISTER_XLS_COMMON_FIELD[3])
                        , ValidatingRegisterTypeEn.emptyFieldName.getCode().toString());
            }

            //取得其他记录，从第5行开始

            if (hssfSheet.getPhysicalNumberOfRows() < 6) {
                throw new BusinessException(String.format(ValidatingRegisterTypeEn.emptyFieldName.getMean(), "人员记录")
                        , ValidatingRegisterTypeEn.emptyFieldName.getCode().toString());
            }

            for (int rowNum = 4; rowNum < hssfSheet.getPhysicalNumberOfRows(); rowNum++) {

                try {

//                    int getLastRowNum = hssfSheet.getLastRowNum();
//                    int getFirstRowNum = hssfSheet.getFirstRowNum();
//                    int getPhysicalNumberOfRows = hssfSheet.getPhysicalNumberOfRows();

                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);

                    if (hssfRow != null) {

                        if (isEndRow(hssfRow)) {
                            break;
                        }

                        int total = hssfRow.getPhysicalNumberOfCells();
                        /*if(total<XlsHeaderConstant.REGISTER_XLS_COMMON_FIELD.length){

                        }*/
                        if (rowNum == 4) { // header

                            //校验header title
                            List<String> xlsTitleList = new ArrayList<>();
                            for (int i = 0; i < total; i++) {
                                String temp = getStringCellValue(hssfRow.getCell(i));
                                xlsTitleList.add(temp);
                            }
                            //校验固定列是否都存在
                            for (String h : XlsHeaderConstant.REGISTER_XLS_HEADERS) {
                                if (!xlsTitleList.contains(h)) {
                                    throw new BusinessException(String.format(ValidatingRegisterTypeEn.emptyFieldName.getMean(), h)
                                            , ValidatingRegisterTypeEn.emptyFieldName.getCode().toString());
                                }
                            }

                            //获取扩展字段标题,列表
                            String[] header = new String[total - XlsHeaderConstant.REGISTER_XLS_HEADERS.length];
                            extSize = total - XlsHeaderConstant.REGISTER_XLS_HEADERS.length;
                            for (int i = 0; i < extSize; i++) {
                                String extName = getStringCellValue(hssfRow.getCell(i + XlsHeaderConstant.REGISTER_XLS_HEADERS.length));
                                header[i] = extName;
                            }
                            dto.setHeader(header);
                            continue;   //直接跳出，进行后续的循环
                        }

                        RegisterTemplateDataDTO dataDto = new RegisterTemplateDataDTO();


                        //获取姓名
                        int index = 0;
                        String name = getStringCellValue(hssfRow.getCell(index));
                        /*if (StringUtils.isBlank(name)) {
                            throw new BusinessException(String.format(ValidatingRegisterTypeEn.emptyFieldByName.getMean(),
                                    rowNum - 4,
                                    XlsHeaderConstant.REGISTER_XLS_HEADERS[index])
                                    , ValidatingRegisterTypeEn.emptyFieldByName.getCode().toString());
                        }*/

                        //获取身份证号码
                        index = 1;
                        String identNumber = getStringCellValue(hssfRow.getCell(index));
                        /*if (StringUtils.isBlank(identNumber)) {
                            throw new BusinessException(String.format(ValidatingRegisterTypeEn.emptyFieldByName.getMean(),
                                    rowNum - 4
                                    , XlsHeaderConstant.REGISTER_XLS_HEADERS[index])
                                    , ValidatingRegisterTypeEn.emptyFieldByName.getCode().toString());
                        }*/

                        //获取银行名称
                        index = 2;
                        String bankName = getStringCellValue(hssfRow.getCell(index));
                        /*if (StringUtils.isBlank(bankName)) {
                            throw new BusinessException(String.format(ValidatingRegisterTypeEn.emptyFieldByName.getMean(),
                                    rowNum - 4,
                                    XlsHeaderConstant.REGISTER_XLS_HEADERS[index])
                                    , ValidatingRegisterTypeEn.emptyFieldByName.getCode().toString());
                        }*/

                        //获取银行卡号
                        index = 3;
                        String bankNumber = getStringCellValue(hssfRow.getCell(index));
                        /*if (StringUtils.isBlank(bankName)) {
                            throw new BusinessException(String.format(ValidatingRegisterTypeEn.emptyFieldByName.getMean(),
                                    rowNum - 4
                                    , XlsHeaderConstant.REGISTER_XLS_HEADERS[index])
                                    , ValidatingRegisterTypeEn.emptyFieldByName.getCode().toString());
                        } else {
                            //校验是否为数字
                            if (!MathUtil.isInteger(bankNumber)) {
                                throw new BusinessException(String.format(ValidatingRegisterTypeEn.formatErrorByRow.getMean()
                                        , rowNum - 4
                                        , XlsHeaderConstant.REGISTER_XLS_HEADERS[index])
                                        , ValidatingRegisterTypeEn.formatErrorByRow.getCode().toString());
                            }
                        }*/

//                        String areaName = hssfRow.getCell(3) == null ? "" : hssfRow.getCell(3).getStringCellValue();
//                        String projectName = hssfRow.getCell(4) == null ? "" : hssfRow.getCell(4).getStringCellValue();
//
//                        int year = (int) hssfRow.getCell(5).getNumericCellValue();
//                        int subsidyCount = (int) hssfRow.getCell(6).getNumericCellValue();
                        index = 4;
                        String moneyStr = getStringCellValue(hssfRow.getCell(index));


                        /*double money = 0;
                        try {
                            money = Double.parseDouble(moneyStr);
                        } catch (Exception ex) {
                            money = 0;
                        }*/
                        /*if (money < 0) {
                            throw new BusinessException(String.format(ValidatingRegisterTypeEn.moneyLessThan0.getMean(), name)
                                    , ValidatingRegisterTypeEn.moneyLessThan0.getCode().toString());
                        }*/

                        dataDto.setName(name);
                        dataDto.setIdentNumber(identNumber);
                        dataDto.setBankName(bankName);
                        dataDto.setBankNumber(bankNumber);
                        dataDto.setAreaName(areaName);   //人员的区划，需要服务端从人员信息中取，暂时存登记区划，通过这里传给服务端
                        dataDto.setProjectName(projectName);
                        dataDto.setYear(year);
                        dataDto.setSubsidyCount(subsidyCount);
//                        dataDto.setMoney(new BigDecimal(money));
                        dataDto.setMoneyStr(moneyStr);
                        //读取扩展属性
                        BigDecimal[] values = new BigDecimal[extSize];
                        for (int i = 0; i < extSize; i++) {
                            HSSFCell hSSFCell = hssfRow.getCell(i + XlsHeaderConstant.REGISTER_XLS_HEADERS.length);
                            if (hSSFCell == null)
                                values[i] = BigDecimal.ZERO;
                            else {
                                try {
                                    values[i] = new BigDecimal(getNumericCellValue(hSSFCell));
                                } catch (Exception ex) {
                                    values[i] = new BigDecimal(0.00);
                                }
                            }
                        }
                        dataDto.setValues(values);
                        list.add(dataDto);
                    }
                } catch (BusinessException ex) {
                    throw ex;
                }
            }
        }

        if (dto.getList() == null || dto.getList().size() == 0) {
            throw new BusinessException("execl中未找到到人员记录");
        }

        return dto;
    }

    @SuppressWarnings("static-access")
    private String getValue(XSSFCell xssfCell) {
        if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(xssfCell.getNumericCellValue());
        } else {
            return String.valueOf(xssfCell.getStringCellValue());
        }
    }

    @SuppressWarnings("static-access")
    private String getValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }


    private String getStringCellValue(HSSFCell cell) {
        if (cell != null) {
            cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
            return cell.getStringCellValue() != null ? cell.getStringCellValue() : "";
        }
        return "";
    }

    private double getNumericCellValue(HSSFCell cell) {
        if (cell != null) {
            cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC);
            try {
                return cell.getNumericCellValue();
            } catch (Exception ex) {
                return 0;
            }
        }
        return 0;
    }

    private Boolean getBooleanCellValue(HSSFCell cell) {
        if (cell != null) {
            cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN);

            try {
                return cell.getBooleanCellValue();
            } catch (Exception ex) {
                return null;
            }
        }
        return null;
    }

    private Boolean isEndRow(HSSFRow hssfRow) {
        boolean ret = true;
        int total = hssfRow.getPhysicalNumberOfCells();
        for (int i = 0; i < total; i++) {
            if (StringUtils.isNotBlank(getStringCellValue(hssfRow.getCell(i)))) {
                ret = false;
                break;
            }
        }
        return ret;
    }

    public static void main(String[] args) {

        BigDecimal bigDecimal = new BigDecimal(-1);

        BigDecimal b = new BigDecimal(new DecimalFormat("#.00").format(bigDecimal));
        System.out.print(b);
        if (bigDecimal.compareTo(new BigDecimal(0)) == -1) {

            System.out.println("111");
        }

        RegisterExcel excel = new RegisterExcel();
        String filePath = "G:\\100017_2018_2_20170113.xls";
        try {
            RegisterTemplateXlsDTO dto = excel.readExcel(filePath);
            String[] header = dto.getHeader();
            System.out.println(JSON.toJSONString(Arrays.asList(header)));

            List<RegisterTemplateDataDTO> list = dto.getList();
            if (list != null)
                System.out.println(JSON.toJSONString(list));
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public String getPostfix(String path) {
        if (path == null || EMPTY.equals(path.trim())) {
            return EMPTY;
        }
        if (path.contains(POINT)) {
            return path.substring(path.lastIndexOf(POINT) + 1, path.length());
        }
        return EMPTY;
    }
}